--CREATE FOREIGN KEY BETWEEN DIFFERENT TABLE

--01.FK_NHANVIEN_PHONGBAN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='FK_NHANVIEN_PHONGBAN')
BEGIN
	ALTER TABLE NHANVIEN
	DROP CONSTRAINT FK_NHANVIEN_PHONGBAN
END
GO
ALTER TABLE NHANVIEN
ADD CONSTRAINT FK_NHANVIEN_PHONGBAN
FOREIGN KEY(PHG)
REFERENCES PHONGBAN(MAPHG)

--02.FK_PHANCONG_NHANVIEN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='FK_PHANCONG_NHANVIEN')
BEGIN
	ALTER TABLE PHANCONG
	DROP CONSTRAINT FK_PHANCONG_NHANVIEN
END
GO
ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_NHANVIEN
FOREIGN KEY(MA_NVIEN)
REFERENCES NHANVIEN(MANV)

--03.FK_PHANCONG_DEAN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='FK_PHANCONG_DEAN')
BEGIN
	ALTER TABLE PHANCONG
	DROP CONSTRAINT FK_PHANCONG_DEAN
END
GO
ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_DEAN
FOREIGN KEY(SODA)
REFERENCES DEAN(MADA)

--04.FK_THANNHAN_NHANVIEN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='FK_THANNHAN_NHANVIEN')
BEGIN
	ALTER TABLE THANNHAN
	DROP CONSTRAINT FK_THANNHAN_NHANVIEN
END
GO
ALTER TABLE THANNHAN
ADD CONSTRAINT FK_THANNHAN_NHANVIEN
FOREIGN KEY(MA_NVIEN)
REFERENCES NHANVIEN(MANV)

--05.FK_DIADIEM_PHG_PHONGBAN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='FK_DIADIEM_PHG_PHONGBAN')
BEGIN
	ALTER TABLE DIADIEM_PHG
	DROP CONSTRAINT FK_DIADIEM_PHG_PHONGBAN
END
GO
ALTER TABLE DIADIEM_PHG
ADD CONSTRAINT FK_DIADIEM_PHG_PHONGBAN
FOREIGN KEY(MAPHG)
REFERENCES PHONGBAN(MAPHG)

--06.FK_PHONGBAN_NHANVIEN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='FK_PHONGBAN_NHANVIEN')
BEGIN
	ALTER TABLE PHONGBAN
	DROP CONSTRAINT FK_PHONGBAN_NHANVIEN
END
ALTER TABLE PHONGBAN
ADD CONSTRAINT FK_PHONGBAN_NHANVIEN
FOREIGN KEY(TRPHG)
REFERENCES NHANVIEN(MANV)